﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;

namespace SQLLib
{
    public class SQLiteExecutor
    {
        #region Data and Properties
        private SQLiteConnection Connection;
        private bool ConnectionIsValid = false;
        private bool IsConnected = false;
        private string LastCommand = "!help";
        

        #endregion //Data and Properties

        #region Constructor
        public SQLiteExecutor()
        {
            LastCommand = "!help";

        }
        public SQLiteExecutor( string connection )
        {
            
        }
        #endregion //Constructor

        #region Methods
        public string ExecuteCommand(string command, out bool error)
        {
            error = false;

            if (command.Length >= 4 &&
                command.Substring(0, 4).Equals("help", StringComparison.CurrentCultureIgnoreCase))
            {
                LastCommand = command;
                return DisplayHelp();
            }
            if (command.Length >= 1 &&
                command[0] == '!')
            {
                return ExecuteControlCommand(command, out error);
            }

            LastCommand = command;
            try
            {
                if( IsConnected && ConnectionIsValid)
                {

                    SQLiteCommand cmd = new SQLiteCommand(command, Connection);
                    var lol = cmd.ExecuteReader();

                    StringBuilder toOutput = new StringBuilder();

                    var fieldcount= lol.FieldCount;
                    string[] columns = new string[fieldcount];
                    Type[] resultTypes = new Type[fieldcount]; 
                    object[] results = new object[fieldcount];
                    for (int i = 0; i < fieldcount; i++)
                    {
                        columns[i] = lol.GetName(i);
                        Console.Write(columns[i] + ",\t");
                        toOutput.Append(columns[i] + ",\t");
                        resultTypes[i] = lol.GetFieldType(i);
                    }
                    Console.Write("\n");
                    toOutput.Append("\n");
                    
                    while (lol.Read())
                    {
                        lol.GetValues(results);

                        for (int i = 0; i < fieldcount; i++)
                        {
                            if( resultTypes[i] == typeof( System.Int32) )
                            {
                                Console.Write((results[i] as Int32?) + ",\t");
                                toOutput.Append((results[i] as Int32?) + ",\t");
                            }
                            else if (resultTypes[i] == typeof(System.Int64))
                            {
                                Console.Write((results[i] as Int64?) + ",\t");
                                toOutput.Append((results[i] as Int64?) + ",\t");
                            }
                            else if (resultTypes[i] == typeof(System.Int16))
                            {
                                Console.Write((results[i] as Int16?) + ",\t");
                                toOutput.Append((results[i] as Int16?) + ",\t");
                            }
                            else if (resultTypes[i] == typeof(System.String))
                            {
                                Console.Write((results[i] as String) + ",\t");
                                toOutput.Append((results[i] as String) + ",\t");
                            }
                            else if (resultTypes[i] == typeof(System.Boolean))
                            {
                                Console.Write((results[i] as Boolean?) + ",\t");
                                toOutput.Append((results[i] as Boolean?) + ",\t");
                            }
                            else if (resultTypes[i] == typeof(System.Decimal))
                            {
                                Console.Write((results[i] as Decimal?) + ",\t");
                                toOutput.Append((results[i] as Decimal?) + ",\t");
                            }
                            else if (resultTypes[i] == typeof(System.DateTime))
                            {
                                Console.Write((results[i] as DateTime?) + ",\t");
                                toOutput.Append((results[i] as DateTime?) + ",\t");
                            }
                            else if (resultTypes[i] == typeof(System.Double))
                            {
                                Console.Write((results[i] as Double?) + ",\t");
                                toOutput.Append((results[i] as Double?) + ",\t");
                            }
                            else if (resultTypes[i] == typeof(System.Byte))
                            {
                                Console.Write((results[i] as Byte?) + ",\t");
                                toOutput.Append((results[i] as Byte?) + ",\t");
                            }
                            else if (resultTypes[i] == typeof(System.Single))
                            {
                                Console.Write((results[i] as Single?) + ",\t");
                                toOutput.Append((results[i] as Single?) + ",\t");
                            }
                            else if (resultTypes[i] == typeof(System.Guid))
                            {
                                Console.Write((results[i] as Guid?) + ",\t");
                                toOutput.Append((results[i] as Guid?) + ",\t");
                            }

                            //TODO:  support the other types
                            else
                            {
                                Console.Write((resultTypes[i]) + " is unhandled,\t");
                                toOutput.Append((resultTypes[i]) + " is unhandled,\t");
                            }
                        }

                        Console.Write("\n");
                        toOutput.Append("\n");
                    }
                    
                    return toOutput.ToString();
                }
            }
            catch (Exception ex)
            {
                error = true;
                return "Something went wrong. Error Message:\n\t " + 
                    ex.Message.Replace("\n", "\n\t");
            }


            return "Not Fully Implemented";
        }
        #region Control Command Methods

        private string ExecuteControlCommand(string command, out bool error)
        {
            if( command.Length == 2 &&
                command.Equals("!!", StringComparison.CurrentCultureIgnoreCase))
            {
                return "Executing Last Command: \"" + LastCommand + "\"\n" + ExecuteCommand(LastCommand, out error);
            }

            LastCommand = command;
            error = false;

            if (command.Length >= 5  &&
                command.Substring(0, 5).Equals("!help", StringComparison.CurrentCultureIgnoreCase))
            {
                return DisplayHelp();
            }
            if (command.Length >= 7 &&
                command.Substring(0, 7).Equals("!status", StringComparison.CurrentCultureIgnoreCase))
            {
                return DisplayStatus();
            }
            if (command.Length == 5 && command.Equals("!exit", StringComparison.CurrentCultureIgnoreCase))
            {
                Environment.Exit(1);
            }
            if (command.Length == 6 && command.Equals("!close", StringComparison.CurrentCultureIgnoreCase))
            {
                try
                {
                    Connection.Close();
                    return "Connection Closed";
                }
                catch (Exception ex)
                {
                    error = true;
                    return "Connection failed to close.  Perhaps we weren't connected?";
                }
            }

            if (command.Length >= 11 &&
                command.Substring(0, 11).Equals("!ConnectTo ", StringComparison.CurrentCultureIgnoreCase))
            {
                return SetConnection(command.Substring(11), out error);
            }
            if (command.Length >= 12 &&
                command.Substring(0, 12).Equals("!Connect To ", StringComparison.CurrentCultureIgnoreCase))
            {
                return SetConnection(command.Substring(12), out error);
            }
            if (command.Length >= 9 &&
                command.Substring(0, 9).Equals("!Connect ", StringComparison.CurrentCultureIgnoreCase))
            {
                return SetConnection(command.Substring(9), out error);
            }
            if (command.Length >= 17 &&
                command.Substring(0, 17).Equals("!Change Password ", StringComparison.CurrentCultureIgnoreCase))
            {
                return ChangePassword(command.Substring(17), out error);
            }
            if (command.Length >= 16 &&
                command.Substring(0, 16).Equals("!ChangePassword ", StringComparison.CurrentCultureIgnoreCase))
            {
                return ChangePassword(command.Substring(16), out error);
            }
            if (command.Length >= 14 &&
                command.Substring(0, 14).Equals("!Set Password ", StringComparison.CurrentCultureIgnoreCase))
            {
                return ChangePassword(command.Substring(14), out error);
            }
            if (command.Length >= 13 &&
                command.Substring(0, 13).Equals("!SetPassword ", StringComparison.CurrentCultureIgnoreCase))
            {
                return ChangePassword(command.Substring(13), out error);
            }
            if (command.Length >= 5 &&
                command.Substring(0, 5).Equals("!run ", StringComparison.CurrentCultureIgnoreCase))
            {
                return RunScriptFile(command.Substring(5), out error);
            }


            return "Not Fully Implemented";
        }
        private string DisplayHelp()
        {
            StringBuilder t = new StringBuilder();
            t.Append("\n");
            t.AppendLine("Control Commands:");
            t.AppendLine("    !HELP\t\t\t\t--Display this list.");
            t.AppendLine("    !STATUS\t\t\t--Display the current status of connected database.");
            t.AppendLine("    !!\t\t\t\t--Repeat the last command.");
            t.AppendLine("    !EXIT\t\t\t\t--Close this program.");
            t.Append("    !CONNECT File[:Password]\t--Connects to the database found in the specified file.  "); 
            t.Append("A password must be provided if the file requires one.\n");
            t.Append("    !CONNECTTO File[:Password]\t--Same as !CONNECT.\n");
            t.Append("    !CONNECT TO File[:Password]\t--Same as !CONNECT.\n");
            t.AppendLine("    !CLOSE\t\t\t--Close the connection to the database.");
            t.AppendLine("    !RUN script file\t\t\t--Runs the commands in a script file.");
            t.AppendLine("    !SET PASSWORD password\t--Sets or Changes the current database's password.");
            t.AppendLine("    !SETPASSWORD password\t--Same as !SET PASSWORD.");
            t.AppendLine("    !CHANGE PASSWORD password\t--Same as !SET PASSWORD.");
            t.AppendLine("    !CHANGEPASSWORD password\t--Same as !SET PASSWORD.");
            t.Append("\n");
            t.AppendLine("SQLite Commands:");
            return t.ToString();

        }
        private string DisplayStatus()
        {
            StringBuilder t = new StringBuilder();
            t.Append("\n");
            t.Append("STATUS: \n    ");
            if( !IsConnected)
            {
                t.Append( "    NOT connected." );
            }
            else
            {
                if( !ConnectionIsValid )
                {
                    t.Append("    Failed to connect to: " + Connection.DataSource);
                    t.Append("");
                    t.Append("\n");
                }
                else
                {
                    t.Append("    Connected to: " + Connection.DataSource);
                    t.Append("");
                    t.Append("\n");
                }
            }

            return t.ToString();
        }
        private string SetConnection(string connection, out bool error)
        {
            
            
            var split = connection.Replace("\"", "").Split(':');
            string filePath = "";
            string password = "";

            if (split.Length == 1)
            {
                filePath = split[0];
            }
            else if (split.Length == 2)
            {
                if (split[0].Length == 1 && split[1][0] == '\\') //if the colon was due to C:\
                {
                    filePath = split[0] + ':' + split[1];
                }
                else
                {
                    filePath = split[0];
                    password = split[1];
                }
            }
            else if (split.Length == 3)
            {
                if (split[0].Length == 1 && split[1][0] == '\\') //if the colon was due to C:\
                {
                    filePath = split[0] + ':' + split[1];
                    password = split[2];
                }
                
            }
            
            
            //Data Source=filename;Version=3;Password=myPassword;
            StringBuilder connectionString = new StringBuilder();
            connectionString.Append("Data Source=");
            connectionString.Append(filePath);
            connectionString.Append(";Version=3;");
            if( password != "")
            {
                connectionString.Append("Password=");
                connectionString.Append(password);
                connectionString.Append(";");
            }

            try
            {
                Connection.Close();
            }
            catch (Exception ex)
            {
                Debug.WriteLine("ERROR: Minor.  Caught an excpetion while closing the current connection.\n"
                    + ex.Message);
                //no op
            }


            Connection = new SQLiteConnection(connectionString.ToString());

            try
            {
                Connection.Open();
            }
            catch (Exception ex)
            {
                ConnectionIsValid = false;
                error = true;
                return "ERROR: Connection Failed!\n  Error message: " + ex.Message;
            }

            error = false;
            IsConnected = true;
            ConnectionIsValid = true;
            return "Connection changed to \"" + filePath + "\".";
        }
        private string ChangePassword(string password, out bool error)
        {
            if (!IsConnected)
            {
                error = true;
                return "Not connected to a databse.";
            }
            if (!ConnectionIsValid)
            {
                error = true;
                return "The current Database connection is invalid.  No change made";
            }

            try
            {
                Connection.ChangePassword(password);
                error = false;
                return "Password Changed";
            }
            catch (Exception ex)
            {
                error = true;
                return "Password Change FAILED.  \n\tError Message:"
                    + ex.Message.Replace("\n", "\n\t");
            }
        }
        private string RunScriptFile(string file, out bool error)
        {
            error = false;
            StringBuilder toOutput = new StringBuilder();

            if( !IsConnected )
            {
                error = true;
                return "Not connected to a Database";
            }
            if( !ConnectionIsValid )
            {
                error = true;
                return "Database Connection is not valid.";
            }

            try
            {
                StreamReader fin = new StreamReader(file);
                var commands = fin.ReadToEnd().Split(';');

                //SQLiteCommand cmd = new SQLiteCommand();
                //cmd.Connection = Connection;
                foreach( string command in commands )
                {
                    if (command.Length > 2)
                    {
                        toOutput.Append(ExecuteCommand(command, out error));
                        toOutput.Append("\n");
                    }
                }

                //error = false;
                return toOutput.ToString();
            }
            catch (Exception ex)
            {
                error = true;
                return "Failed to open script file.  \n\tError Message: \n\t"
                    + ex.Message.Replace("\n", "\n\t");
            }
        }
        #endregion //Control Command Methods

        #region SQLite Command Methods
        #endregion //SQLite Command Methods

        #endregion //Methods
    }
}
